﻿exports.DBUtils = {
    connection: null,
    getSqlType: function (obj){
        if (typeof (obj) == "string")
            return "'" + obj + "'";
        else if (typeof (obj) == "number")
            return obj;
    },
    jsonToClause: function (jsonObject){
        if (jsonObject) {
            var clause = "";
            var prefix = "";
            for (var key in jsonObject) {
                if (jsonObject[key]) {
                    if (typeof (jsonObject[key]) == "string"||typeof (jsonObject[key]) == "number")
                        clause += prefix + key + "="+ this.getSqlType(jsonObject[key]);
                    else if (typeof (jsonObject[key]) == "object") {//keep consistency with mongodb
                        if (jsonObject[key].$lt) {
                            clause += prefix + key + " < " + this.getSqlType(jsonObject[key].$lt);
                        } else if (jsonObject[key].$gt) {
                            clause += prefix + key + " > " + this.getSqlType(jsonObject[key].$gt);
                        } else if (jsonObject[key].$lte) {
                            clause += prefix + key + " <= " + this.getSqlType(jsonObject[key].$lte);
                        } else if (jsonObject[key].$gte) {
                            clause += prefix + key + " >= " + this.getSqlType(jsonObject[key].$gte);
                        }
                    } else {
                        continue;
                    }
                    prefix = " and ";
                }
            }
            console.log(clause);
            return clause;
        }
        return null;
    },
    setConnection: function (db) {
        this.connection = db;
    },
    getConnection: function (cinfo, object) {
        var mysql = require('mysql');
        var db = mysql.createConnection({
            host     : cinfo.host,
            port     : cinfo.port,
            user     : cinfo.user,
            password : cinfo.password,
            database : cinfo.database,
            connectTimeout: cinfo.connectTimeout || 10000
        });
        var p = new Promise(function (resolve, reject) {
            try {
                db.connect();
                resolve({ result: db ,object: object});
            } catch (e) {
                reject(e);
            }
        });
        return p;
    },
    insert: function (db, table, data, object) {
        var query = "INSERT INTO `" + table + "` SET ?";
        var p = new Promise(function (resolve, reject) {
            db.query(query, data, function (err, rows, fields) {
                if (err) {
                    reject(err);
                }
                else {
                    resolve(object);
                }
            });
        });
        return p;
    },
    
    find: function (db, table, data, object) {
        var query = "select * from `" + table + "`";
        var clause = this.jsonToClause(data);
        if (clause) {
            query += " where " + clause;
        }
        var p = new Promise(function (resolve, reject) {
            db.query(query, function (err, rows, fields) {
                if (err) {
                    reject(err);
                }
                else {
                    resolve({ result: rows, object: object });
                }
            });
        });
        return p;
    },
    close: function (db) {
        db.destroy();
    }
}